Append and Delete Methods Example

This example uses either the Append method or the Delete method to modify the Fields collection of a TableDef. The AppendDeleteField procedure is required for this procedure to run.

Sub AppendX()

   Dim dbsNorthwind As Database
   Dim tdfEmployees As TableDef
   Dim fldLoop As Field

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   Set tdfEmployees = dbsNorthwind.TableDefs!Employees

   ' Add three new fields.
   AppendDeleteField tdfEmployees, "APPEND", _
      "E-mail", dbText, 50
   AppendDeleteField tdfEmployees, "APPEND", _
      "Http", dbText, 80
   AppendDeleteField tdfEmployees, "APPEND", _
      "Quota", dbInteger, 5

   Debug.Print "Fields after Append"
   Debug.Print , "Type", "Size", "Name"

   ' Enumerate the Fields collection to show the new fields.
   For Each fldLoop In tdfEmployees.Fields
      Debug.Print , fldLoop.Type, fldLoop.Size, fldLoop.Name
   Next fldLoop

   ' Delete the newly added fields.
   AppendDeleteField tdfEmployees, "DELETE", "E-mail"
   AppendDeleteField tdfEmployees, "DELETE", "Http"
   AppendDeleteField tdfEmployees, "DELETE", "Quota"

   Debug.Print "Fields after Delete"
   Debug.Print , "Type", "Size", "Name"

   ' Enumerate the Fields collection to show that the new 
   ' fields have been deleted.
   For Each fldLoop In tdfEmployees.Fields
      Debug.Print , fldLoop.Type, fldLoop.Size, fldLoop.Name
   Next fldLoop

   dbsNorthwind.Close

End Sub

Sub AppendDeleteField(tdfTemp As TableDef, _
   strCommand As String, strName As String, _
   Optional varType, Optional varSize)

   With tdfTemp

      ' Check first to see if the TableDef object is 
      ' updatable. If it isn't, control is passed back to 
      ' the calling procedure.
      If .Updatable = False Then
         MsgBox "TableDef not Updatable! " & _
            "Unable to complete task."
         Exit Sub
      End If

      ' Depending on the passed data, append or delete a
      ' field to the Fields collection of the specified
      ' TableDef object.
      If strCommand = "APPEND" Then
         .Fields.Append .CreateField(strName, _
            varType, varSize)
      Else
         If strCommand = "DELETE" Then .Fields.Delete _
            strName
      End If

   End With

End Sub